﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using PresentationControls;
using System.Reflection;
using System.IO;
using System.Globalization;
using System.Collections;
using System.Diagnostics;
using iTextSharp;
using iTextSharp.text.pdf;
using iTextSharp.text;
using System.Text.RegularExpressions;
using System.Xml.Serialization;

namespace Lab_Report_Search_Tool
{
    public partial class Form1 : Form
    {
        int fieldNameIndex = 0;
        int fieldDefaultIndex = 5;

        String[][] FieldsArray;
        String[][] FilterArray;

        private static int filterCount = 7;
        private ComboBox[] Field = new ComboBox[filterCount];
        private ComboBox[] Filter = new ComboBox[filterCount];
        private CheckBoxComboBox[] Value = new CheckBoxComboBox[filterCount];
        private PictureBox[] valid = new PictureBox[filterCount];


        public Form1()
        {
            InitializeComponent();

            drawFilterBoxes(); 

            LoadFields();
            LoadFilters();

            updateFilterOptions();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        #region UI

        #region UI - Step 1 Choosing Fields
       
        private void CustomerListBox_DoubleClick(object sender, EventArgs e)
        {
            if (CustomerListBox.SelectedItem != null)
            {
                SelectedItemsListBox.Items.Add(CustomerListBox.SelectedItem.ToString());
                FieldsArray[getFieldsArrayIndex(CustomerListBox.SelectedItem.ToString())][6] = "0";
                CustomerListBox.Items.Remove(CustomerListBox.SelectedItem.ToString());
                updateFilterOptions();  
            }
        }

        private void SelectedItemsListBox_DoubleClick(object sender, EventArgs e)
        {
            if (SelectedItemsListBox.SelectedItem != null)
            {
                CustomerListBox.Items.Add(SelectedItemsListBox.SelectedItem.ToString());
                FieldsArray[getFieldsArrayIndex(SelectedItemsListBox.SelectedItem.ToString())][6] = "1";
                SelectedItemsListBox.Items.Remove(SelectedItemsListBox.SelectedItem.ToString());
                updateFilterOptions();
            }
        }

        #endregion UI - Step 1 Choosing Fields

        #region UI - Step 2 Set Filters

        private void drawFilterBoxes()
        {
            int xStart = 10;
            int yStart = 20;

            int xSpot = xStart;
            int ySpot = yStart;

            int xMargin = 5;
            int yMargin = 5;


            for (int i = 0; i < Field.Length; i++)
            {
                xSpot = xStart;

                Field[i] = new ComboBox();
                Field[i].Name = "Fields_" + i;
                Field[i].Location = new Point(xSpot, ySpot);
                xSpot += xMargin + Field[i].Width;
                Field[i].TextChanged += Control_Clicks;
                Field[i].KeyPress += myCombo_KeyPress;
                StepTwoGroupBox.Controls.Add(Field[i]);

                Filter[i] = new ComboBox();
                Filter[i].Name = "Filter_" + i;
                Filter[i].Location = new Point(xSpot, ySpot);
                xSpot += xMargin + Filter[i].Width;
                Filter[i].Show();
                Filter[i].TextChanged += Control_Clicks;
                StepTwoGroupBox.Controls.Add(Filter[i]);


                Value[i] = new CheckBoxComboBox();
                Value[i].Name = "Value_" + i;
                Value[i].Location = new Point(xSpot, ySpot);
                Value[i].TextChanged += Control_Clicks;
                xSpot += xMargin + Value[i].Width;
                StepTwoGroupBox.Controls.Add(Value[i]);

                valid[i] = new PictureBox();
                valid[i].Name = "valid_" + i;
                valid[i].Location = new Point(xSpot, ySpot);
                valid[i].Size = new Size(20, 20);
                xSpot += xMargin + valid[i].Width;
                valid[i].Visible = true;
                valid[i].Image = valid[i].Image = Lab_Report_Search_Tool.Properties.Resources.x;
                StepTwoGroupBox.Controls.Add(valid[i]);

                ySpot += yMargin + Field[i].Height;
            }
        }

       

        private void myCombo_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = true;
        }

        private void Control_Clicks(object sender, EventArgs e)
        {
            Control control = (Control)sender;
            String name = control.Name.ToString();
            String controlType = name.Split('_')[0];
            int index = int.Parse(name.Split('_')[1]);

            ComboBox cb = null;
            if (control.GetType().ToString() == "System.Windows.Forms.ComboBox")
            {
                cb = control as ComboBox;

            }

            /* 
             * if field then update filter and value, validate? 
             * 
             * if filter then validate 
             * 
             * 
             * if value then validate 
             * 
              * */
            if (controlType == "Fields" && Field[index].Text != "")
            {
                //filter type 
                Filter[index].Text = "";
                Filter[index].Items.Clear();
                int idx = getFieldsArrayIndex(Field[index].SelectedItem.ToString());
                String FilterType = FieldsArray[idx][3].Split(' ')[0].ToLower();
                //MessageBox.Show(FieldsArray[idx][3].Split(' ')[0].ToLower() + "     -    " + Field[index].SelectedItem.ToString()); 
                for (int i = 0; i < FilterArray.Length; i++)
                {
                    if (FilterType.ToLower() == FilterArray[i][0].ToLower())
                    {
                        Filter[index].Items.Add(FilterArray[i][1]);
                    }
                }
                //filter value 
                if (FieldsArray[idx][3].ToLower() == "date")
                {
                    Value[index].AllowDrop = false;
                    Value[index].Text = DateTime.Today.ToString("MM/dd/yyyy");
                    //Value[index].Text = "--/--/----";
                    Value[index].DropDownHeight = 1;
                    Filter[index].Text = "after or on ";

                }
                else if (FilterType == "select")
                {
                    fillListChoices(FieldsArray[idx][0], index);
                    Value[index].Text = "";
                    Value[index].DropDownHeight = 250;
                    Filter[index].Text = "in";
                }
                else
                {
                    Value[index].AllowDrop = false;
                    Value[index].Text = "";
                    Value[index].DropDownHeight = 1;
                    Filter[index].Text = "contains";
                }

            }
            ValidateFilters();

        }

        private void ResetFilters_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < Value.Length; i++)
            {
                Value[i].Text = "";
                Value[i].ClearSelection();
            }
        }

        private void resetFilters2_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < Value.Length; i++)
            {
                Value[i].Text = "";
                Value[i].ClearSelection();
                Field[i].Text = "";
                Field[i].SelectedIndex = -1;
                Filter[i].Text = "";
                Filter[i].SelectedIndex = -1;
            }
        }

        private void StepTwoNextButton_Click(object sender, EventArgs e)
        {
            //check if drugs included
            int includeDrugs = 0;
            foreach ( string[] fielditem in FieldsArray)
            {
                    if(fielditem[2] == "SampleDrugs" && SelectedItemsListBox.Items.Contains(fielditem[0]))
                    {
                        includeDrugs = 1;
                    }
            }
            
            //build where clause
            int First = 1;
            String FilterTemp = "";
            String ValueTemp = "";
            string whereClause = ""; 
            for (int i = 0; i < filterCount; i++)
            {
                FilterTemp = Filter[i].Text;
                ValueTemp = Value[i].Text;
                if (valid[i].Width == 51)
                {
                    if (First == 1) { whereClause += "WHERE 1=1 "; First = 0; }
                    if (isDate(Value[i].Text))
                    {
                        whereClause += "and " + CSVtoSQL(Field[i].Text + "_" + Filter[i].Text + "_" + getSqlDate(DateTime.Parse(Value[i].Text)));
                    }
                    else
                    {
                        String tempq = "and " + CSVtoSQL(Field[i].Text + "_" + Filter[i].Text + "_" + Value[i].Text.Replace(", ", ","));

                        if (Filter[i].Text.Contains("equals") && Value[i].Text.Contains(","))
                        {
                            tempq = tempq.Replace(",", "','");
                            tempq = tempq.Replace("=", " in (");
                            tempq = tempq.Replace("!", " not ");
                            tempq += ")";

                        }

                        whereClause += tempq;
                    }

                }

            }
            int mostRecent=0; 
            if (latestreportscheckBox1.Checked)
            {
                mostRecent=1;
            }
            whereClause = whereClause.Replace("WHERE 1=1 and", " WHERE ");
            
            //call db and get results
            string[][] results = dbConnection.getData(includeDrugs, whereClause, mostRecent); 
            //build grid
            buildGrid(results); 
        }

        private int getSqlDate(DateTime date)
        {
            int returnval = date.Year * 10000 + date.Month * 100 + date.Day;
            //MessageBox.Show(returnval.ToString());
            return returnval;
        } 
         
        private String CSVtoSQL(String csv)
        {
            String Field = csv.Split('_')[0].Replace("'", "''");
            String FieldSQL = FieldsArray[getFieldsArrayIndex(Field)][0];
            String Filtertext = csv.Split('_')[1].Replace("'", "''");
            String Filtervalue = csv.Split('_')[2].Replace("'", "''");

            String Filtertype = FieldsArray[getFieldsArrayIndex(Field)][3];

            //MessageBox.Show(Field + ", " + Filtertext + ", " + Filtervalue + ", " + Filtertype + " for filter:" + FieldsArray[getFieldsArrayIndex(Field), 1]); 
            for (int numRows = 0; numRows < FilterArray.Length; numRows++)
            {
                // MessageBox.Show((FilterArray[numRows, 0] == Filtertype.Split('-')[0]).ToString() +" "+ (FilterArray[numRows, 1] == Filtertext)).ToString();
                if (FilterArray[numRows][0] == Filtertype.Split('-')[0] && FilterArray[numRows][1] == Filtertext)
                {
                    if (Filtertype.Split('-')[0] == "List")
                    {
                        return (" ["+FieldSQL + "] " + FilterArray[numRows][2].Replace("$$$", Filtervalue)).Replace(",", "','");
                    }
                    else
                    {
                        return " [" + FieldSQL + "] " + FilterArray[numRows][2].Replace("$$$", Filtervalue);
                    }
                }
            }

            return "NOT FOUND" + csv;

        }

        #endregion UI - Step 2 Set Filters

        #region UI - Step 3 Results

        private void buildGrid(string[][] queryResults)
        {
            ResultsdataGridView1.DataSource = null;
            this.ResultsdataGridView1.Rows.Clear();
            ResultsdataGridView1.Columns.Clear();
            ResultsdataGridView1.Refresh();

            StepThreeGroupBox.Visible = true;              

            ArrayList arrList = new ArrayList();
            System.Data.DataTable dt = new DataTable();
            
            DataGridViewButtonColumn col = new DataGridViewButtonColumn();
            col.UseColumnTextForButtonValue = true;
            col.Text = "ADD";
            col.Name = "Report";
            col.Width = 45;
            col.Frozen = true;
            ResultsdataGridView1.Columns.Add(col);

            DataGridViewButtonColumn col2 = new DataGridViewButtonColumn();
            col2.UseColumnTextForButtonValue = true;
            col2.Text = "View";
            col2.Name = "Report";
            col2.Width = 45;
            col2.Frozen = true;
            ResultsdataGridView1.Columns.Add(col2);


            DataGridViewButtonColumn col4 = new DataGridViewButtonColumn();
            col4.UseColumnTextForButtonValue = true;
            col4.Text = "ADD";
            col4.Name = "Chain";
            col4.Width = 45;
            col4.Frozen = true;
            ResultsdataGridView1.Columns.Add(col4);

            DataGridViewButtonColumn col3 = new DataGridViewButtonColumn();
            col3.UseColumnTextForButtonValue = true;
            col3.Text = "View";
            col3.Name = "Chain";
            col3.Width = 45;
            col3.Frozen = true;
            ResultsdataGridView1.Columns.Add(col3);

            foreach (string item in queryResults[0])
            {
                dt.Columns.Add(item.ToString());
            }  //Pass array object to LoadDataRow method
             

            for (int i = 1; i < queryResults.Length; i++)
            {
                
                dt.LoadDataRow(queryResults[i], true);
            }


           
            ResultsdataGridView1.DataSource = dt;

            for (int i = 0; i < ResultsdataGridView1.Columns.Count; i++)
            {
                ResultsdataGridView1.Columns[i].Visible = false;
                if (getFieldsArrayIndex(ResultsdataGridView1.Columns[i].Name) != -1)
                {
                    ResultsdataGridView1.Columns[i].Width = int.Parse(FieldsArray[getFieldsArrayIndex(ResultsdataGridView1.Columns[i].Name)][4]);
                    if (SelectedItemsListBox.Items.Contains(ResultsdataGridView1.Columns[i].Name))//FieldsArray[getFieldsArrayIndex(ResultsdataGridView1.Columns[i].Name)][5] == "1")
                    {
                       // MessageBox.Show(ResultsdataGridView1.Columns[i].Name + "found");
                        ResultsdataGridView1.Columns[i].Visible = true;
                    }
                    else
                    {
                      //  MessageBox.Show(ResultsdataGridView1.Columns[i].Name + " NOT SELECTED"); 
                    }
                }
                else
                {
                   // MessageBox.Show(ResultsdataGridView1.Columns[i].Name + " NOT found"); 
                }

                
            }
             

            ResultsdataGridView1.Columns[0].Visible = true;
            ResultsdataGridView1.Columns[1].Visible = true;
            ResultsdataGridView1.Columns[2].Visible = true;
            ResultsdataGridView1.Columns[3].Visible = true;
            ResultsdataGridView1.Columns["ReportVersion"].Visible = true;
            StepThreeGroupBox.Width = this.Width - 35;
            ResultsdataGridView1.Width = StepThreeGroupBox.Width - 25;
            CheckBlankButtons();
            ResultsdataGridView1.Refresh();
        }

        private void CheckBlankButtons()
        {
            for (int numRows = 0; numRows < ResultsdataGridView1.Rows.Count; numRows++)
            {
                if (ResultsdataGridView1.Rows[numRows].Cells["url"].Value.ToString() == "")
                {
                    DataGridViewButtonCell b = ResultsdataGridView1.Rows[numRows].Cells[0] as DataGridViewButtonCell;
                    b.UseColumnTextForButtonValue = false;
                    b = ResultsdataGridView1.Rows[numRows].Cells[1] as DataGridViewButtonCell;
                    b.UseColumnTextForButtonValue = false;
                }

                if (ResultsdataGridView1.Rows[numRows].Cells["coc"].Value.ToString() == "")
                {
                    DataGridViewButtonCell b = ResultsdataGridView1.Rows[numRows].Cells[2] as DataGridViewButtonCell;
                    b.UseColumnTextForButtonValue = false;
                    b = ResultsdataGridView1.Rows[numRows].Cells[3] as DataGridViewButtonCell;
                    b.UseColumnTextForButtonValue = false;
                }
            }
        }

        private void ResultsdataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex == -1) { return; }
            if (e.ColumnIndex == -1) { return; }
            Int32 selectedRowCount = ResultsdataGridView1.Rows.GetRowCount(DataGridViewElementStates.Selected);
            String url = ResultsdataGridView1.Rows[e.RowIndex].Cells["url"].Value.ToString();
            String Chain = ResultsdataGridView1.Rows[e.RowIndex].Cells["coc"].Value.ToString();
            String market = ResultsdataGridView1.Rows[e.RowIndex].Cells["primaryMarkethide"].Value.ToString();
            if (e.ColumnIndex == 0)
            {
                if (url != "")
                {
                    listBox1.Items.Add(url + "," + market);
                }

            }
            else if (e.ColumnIndex == 1)
            {
                if (url != "")
                {
                    openUrl(url);
                }
            }
            else if (e.ColumnIndex == 2)
            {
                if (Chain != "")
                {
                    listBox1.Items.Add(Chain + "," + market);
                }
            }
            else if (e.ColumnIndex == 3)
            {
                if (Chain != "")
                {
                    openUrl(Chain);
                }
            }
            else { }


        }

        private void addallreportsbutton2_Click(object sender, EventArgs e)
        {
            String url = "";
            String market = "";

            for (int numRows = 0; numRows < ResultsdataGridView1.Rows.Count; numRows++)
            {
                if (ResultsdataGridView1.Rows[numRows].Cells["url"].Value.ToString() != "")
                {
                    url = ResultsdataGridView1.Rows[numRows].Cells["url"].Value.ToString();
                    market = ResultsdataGridView1.Rows[numRows].Cells["primaryMarkethide"].Value.ToString();
                    listBox1.Items.Add(url + "," + market);
                }
            }
        }

        private void addallchainsbutton3_Click(object sender, EventArgs e)
        {
            String Chain = "";
            String market = "";

            for (int numRows = 0; numRows < ResultsdataGridView1.Rows.Count; numRows++)
            {
                if (ResultsdataGridView1.Rows[numRows].Cells["coc"].Value.ToString() != "")
                {
                    Chain = ResultsdataGridView1.Rows[numRows].Cells["coc"].Value.ToString();
                    market = ResultsdataGridView1.Rows[numRows].Cells["primaryMarkethide"].Value.ToString();
                    listBox1.Items.Add(Chain + "," + market);
                }
            }
        }

        private void addallbothbutton4_Click(object sender, EventArgs e)
        {
            String url = "";
            String Chain = "";
            String market = "";

            for (int numRows = 0; numRows < ResultsdataGridView1.Rows.Count; numRows++)
            {
                if (ResultsdataGridView1.Rows[numRows].Cells["url"].Value.ToString() != "")
                {
                    url = ResultsdataGridView1.Rows[numRows].Cells["url"].Value.ToString();
                    market = ResultsdataGridView1.Rows[numRows].Cells["primaryMarkethide"].Value.ToString();
                    listBox1.Items.Add(url + "," + market);
                }

                if (ResultsdataGridView1.Rows[numRows].Cells["coc"].Value.ToString() != "")
                {
                    Chain = ResultsdataGridView1.Rows[numRows].Cells["coc"].Value.ToString();
                    market = ResultsdataGridView1.Rows[numRows].Cells["primaryMarkethide"].Value.ToString();
                    listBox1.Items.Add(Chain + "," + market);
                }
            }

        }

        private void Exportbutton_Click(object sender, EventArgs e)
        {
            String Timestamp = string.Format("{0:yyyy-MM-dd_hh-mm-ss-tt}", DateTime.Now);
            String dir = "\\\\sbs1\\Shared\\Temp\\" + Timestamp + "\\";
            String FinalPath = dir + "QueryResults.xls";
            System.IO.Directory.CreateDirectory(dir);
            export_datagridview_to_excel(ResultsdataGridView1, FinalPath); // @"//sbs1/Shared/Temp/test.xls");
            System.Diagnostics.Process.Start(FinalPath);
        }

        public void export_datagridview_to_excel(DataGridView dgv, string excel_file)
        {
            int cols;
            //open file 
            //StreamWriter wr = new StreamWriter(excel_file);
            using (StreamWriter wr = File.AppendText(excel_file))
            {
                //determine the number of columns and write columns to file 
                cols = dgv.Columns.Count;
                for (int i = 4; i < cols; i++)
                {
                    if (dgv.Columns[i].Visible == true)
                    {
                        wr.Write(dgv.Columns[i].Name.ToString().ToUpper() + "\t");
                    }
                }

                wr.WriteLine();

                //write rows to excel file 
                for (int i = 0; i < (dgv.Rows.Count); i++)
                {
                    for (int j = 4; j < cols; j++)
                    {
                        if (dgv.Columns[j].Visible == true)
                        {
                            if (dgv.Rows[i].Cells[j].Value != null)
                                wr.Write(dgv.Rows[i].Cells[j].Value + "\t");
                            else
                            {
                                wr.Write("\t");
                            }
                        }

                    }

                    wr.WriteLine();
                }

                //close file 
                wr.Close();
            }
        } 
        
        #endregion UI - Step 3 Results

        #region UI - Step 4 Shopping Cart

        private void Removebutton4_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem == null) { return; }
            listBox1.Items.Remove(listBox1.SelectedItems[0]);
        }

        private void removeallbutton5_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
        }

        private void LisboxMoveUp_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem == null)
            { return; }


            var idx = listBox1.SelectedIndex;
            var elem = listBox1.SelectedItem;
            if (idx > 0)
            {
                listBox1.Items.RemoveAt(idx);
                listBox1.Items.Insert(idx - 1, elem);
                listBox1.SetSelected(idx - 1, true);
            }
        }

        private void ListboxMoveDown_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem == null)
            { return; }


            var idx = listBox1.SelectedIndex;
            var elem = listBox1.SelectedItem;
            if (idx < listBox1.Items.Count - 1)
            {
                listBox1.Items.RemoveAt(idx);
                listBox1.Items.Insert(idx + 1, elem);
                listBox1.SetSelected(idx + 1, true);
            }
        }

        private void ViewURL_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem == null)
            { return; }

            openUrl(listBox1.SelectedItem.ToString().Split(',')[0]);
        }

        private void listBox1_DoubleClick(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem == null)
            { return; }

            openUrl(listBox1.SelectedItem.ToString());
        }

        private void Merge_and_open(object sender, EventArgs e)
        {
            //String FinalName = DownloadAndMerge();
            //System.Diagnostics.Process.Start(FinalName);
            string[] urls = new string[listBox1.Items.Count];

            for (int i = 0; i < listBox1.Items.Count; i++)
            {
                urls[i] = (string)listBox1.Items[i];
            }
            string Timestamp = string.Format("{0:yyyy-MM-dd_hh-mm-ss-tt}", DateTime.Now);
            PdfMerger merger = new PdfMerger(urls, "SearchResults", "\\\\sbs1\\Shared\\Temp\\" + Timestamp + "\\");
            if (merger.Merge() == 0)
            {
                System.Diagnostics.Process.Start(merger.getFullPath);
            }

        }

        private void Merge_and_email(object sender, EventArgs e)
        {
            string[] urls = new string[listBox1.Items.Count];

            for (int i = 0; i < listBox1.Items.Count; i++)
            {
                urls[i] = (string)listBox1.Items[i];
            }
            string Timestamp = string.Format("{0:yyyy-MM-dd_hh-mm-ss-tt}", DateTime.Now);
            PdfMerger merger = new PdfMerger(urls, "SearchResults", "\\\\sbs1\\Shared\\Temp\\" + Timestamp + "\\");
            if (merger.Merge() == 0)
            {
                //System.Diagnostics.Process.Start("C:\\Program Files (x86)\\Microsoft Office\\Office15\\OUTLOOK.EXE", "/a \"" + merger.getFullPath + "\" /m name@address.com");
                System.Diagnostics.Process.Start(merger.getFullPath); 
            }


            //String FinalName = DownloadAndMerge();
            //email; //System.Diagnostics.Process.Start(FinalName);
           
        }


        #endregion UI - Step 4 Shopping Cart

        #region UI Private Methods

        private void openUrl(string url)
        {
            if (url != "")
            {
                ProcessStartInfo sInfo = new ProcessStartInfo(url);
                Process.Start(sInfo);
            }

        }

        private void updateFilterOptions()
        {
            foreach (ComboBox dropdownBox in Field)
                {
                    dropdownBox.Items.Clear(); 
                    foreach (string item in SelectedItemsListBox.Items)
                    {
                        dropdownBox.Items.Add(item.ToString());
                    }
                }
        }

       
        private void ValidateFilters()
        {
            for (int x = 0; x < Field.Length; x++)
            {
                if (ValidateFilter(Field[x].Text, Filter[x].Text, Value[x].Text))
                {
                    valid[x].Image = Lab_Report_Search_Tool.Properties.Resources.check;//Lab_Report_Search_Tool.Properties.Resources("x.png");//System.Drawing.Image.FromFile("\\\\sbs1\\shared\\Temp\\check.gif");
                    valid[x].Width = 51;
                }
                else
                {

                    valid[x].Image = Lab_Report_Search_Tool.Properties.Resources.x;
                    valid[x].Width = 50;
                }
            }
        }

        private bool ValidateFilter(String fieldtxt, String filterTxt, String valuetxt)
        {
            if (fieldtxt != "")
            {
                if (FieldsArray[getFieldsArrayIndex(fieldtxt)][5] == "1")
                {
                    if (filterTxt != "")
                    {
                        if (filterTxt.Contains("blank") || FieldsArray[getFieldsArrayIndex(fieldtxt)][3] != "Date" && valuetxt != "" || FieldsArray[getFieldsArrayIndex(fieldtxt)][3] == "Date" && isDate(valuetxt))
                        {
                            return true;
                        }
                        else
                        {
                            ThrowError("no valid value given");
                        }
                    }
                    else
                    {
                        ThrowError("no filter type was selected ");
                    }
                }
                else
                {
                    ThrowError("This Fields was not selected in the selected list ");
                }
            }
            else
            {
                ThrowError("no field was selected ");
            }
            return false;
        }

        private bool isDate(String dateTime)
        {
            string[] formats = { "M/d/yyyy", "MM/d/yyyy", "MM/dd/yyyy", "M/dd/yyyy", "M/d/yy", "MM/d/yy", "MM/dd/yy", "M/dd/yy" };
            DateTime parsedDateTime;
            return DateTime.TryParseExact(dateTime, formats, new CultureInfo("en-US"), DateTimeStyles.None, out parsedDateTime);
        }


        private void ThrowError(String errorMessage)
        {
            Console.Write(errorMessage + '\n');
        }

        private int fillListChoices(string displayName, int FilterIndex )
        {
            string csv = this.getList(displayName);
            string[] splitcsv = csv.Split('^');
            foreach(string item in splitcsv)
            {
                if ( !item.Trim().Equals(""))
                {
                    Value[FilterIndex].Items.Add(item); 
                }
            }
            return 0;
            
        }

        private int getFieldsArrayIndex(String str)
        {
           // MessageBox.Show(" getFieldsArrayIndex " + str);
            
            for (int numRows = 0; numRows < FieldsArray.Length; numRows++)
            {
               // MessageBox.Show(str + "  " + FieldsArray[numRows][0] + "  " + numRows); 
                if (FieldsArray[numRows][0].ToLower().Trim() == str.ToLower().Trim())
                {
                   // MessageBox.Show(str + "  " + FieldsArray[numRows][0] + "  "+ numRows ); 
                    return numRows;
                }
            }
           // MessageBox.Show(str + "  not found"); 
            return -1;
        }


        #endregion UI Private Methods

        #endregion UI

        #region Handling dbConnection
         
        private int LoadFields()
        {
            FieldsArray = dbConnection.getFields();
            foreach (string[] f in FieldsArray)
            {
                if (f[fieldDefaultIndex] == "1")
                {
                    SelectedItemsListBox.Items.Add(f[fieldNameIndex]);
                }
                else
                {
                    CustomerListBox.Items.Add(f[fieldNameIndex]);
                }
            }
            return 0; 
        }

        private int LoadFilters()
        {

            FilterArray = dbConnection.getFilters();
            
            return 0;
        }

        private string getList(string DisplayName)
        {
            string csv="";
            csv = dbConnection.getList(DisplayName);
           // MessageBox.Show(csv);
            return csv; 
        }

        #endregion Handling dbConnection

        

       
    }
}
